﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;



using ES_Project.Model;
using MySql.Data.MySqlClient;
using System.Data;
using System.Web.Configuration;
namespace ES_Project.Pages
{
    public partial class WebForm32 : System.Web.UI.Page
    {
        string StrConn = WebConfigurationManager.ConnectionStrings["baseessystem"].ToString();
        MySqlCommand objCmd = new MySqlCommand(); 
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            { 
                //BindData();
                //CMD_INSERT.Enabled = true;
                //CMD_EDIT.Enabled = false;
                
            }
            
        }
        
        
       
        protected void CMD_EmpCode_Click(object sender, EventArgs e)
        {

            using (essystemEntities context = new essystemEntities())
            {
                List<emp_mast> crm2 = context.emp_mast.Where("it.[emp_code]= '" + empCode.Text + "' ").ToList();
                if (crm2.Count() == 1)
                {
                    emp_mast em = context.emp_mast.Where("it.[emp_code]='" + empCode.Text + "'").First();
                    if (em.emp_code != null)
                    {
                        empName.Text = em.emp_name;
                        empId.Text = Convert.ToString(em.emp_id);

                    }
                }
            }
        }
        protected void CMD_CourseCode_Click(object sender, EventArgs e)
        {

            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


           strSQL = "SELECT * FROM  cls_register_detail crd  "+
                     "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  "+
                     "where crm.cls_reg_code = '"+ clsRegCode.Text+"  '";
            //strSQL = "SELECT * FROM emp_cls_mast ecm left outer join cls_register_detail crd  "+
            //            "on ecm.cls_reg_detail_id = crd.cls_reg_detail_id  "+
            //            "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  "+
            //            "where crm.cls_reg_code = '"+clsRegCode.Text+"'";



            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            //dtAdapter.Fill(dt);
            da4 = null;
            Conn.Close();
            Conn = null;
            //dtAdapter = null;
            //objConn.Close();
            //objConn = null;
            

            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                
                clsName.Text = (string)ds4.Tables[0].Rows[0]["cls_name"].ToString();
                clsCourseTime.Text = (string)ds4.Tables[0].Rows[0]["cls_course_time"].ToString();
                

            }
            

        }
        protected void CMD_INSERT_Click(object sender, EventArgs e)
        {
            using (ES_Project.Model.essystemEntities context = new ES_Project.Model.essystemEntities())
            {
                Button bt = (Button)sender;
                switch (bt.ID)
                {
                    case "CMD_INSERT":
                        emp_cls_mast ecm = new emp_cls_mast();
                        ecm.emp_id = Convert.ToInt32(empId.Text);
                        ecm.emp_cls_day = empClsDay.Text;
                        //ecm.emp_cls_total_hours = empClsTotalHours.Text;
                        //ecm.emp_cls_hours = empClsHours.Text;
                        //ecm.emp_cls_start = empClsStart.Text;
                        //ecm.emp_cls_end = empClsEnd.Text;
                       // ecm.cls_reg_detail_id = Convert.ToInt32(clsRegDetailId.Text);
                       // ecm.cls_reg_detail_id = Convert.ToInt32(myDDL1.SelectedValue);
                        context.emp_cls_mast.AddObject(ecm);
                       // context.SaveChanges();
                        if (context.SaveChanges() > 0)
                        {
                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert1", "alert('บันทึกเรียบร้อยแล้ว')", true);
                            BindData();
                            //GridView4.DataBind();
                            //GridView4.SelectedIndex = -1;
                            empCode.Text = "";
                            empName.Text = "";
                            empClsDay.Text = "";
                            empId.Text = "";
                            //empClsEnd.Text = "";
                            //empClsStart.Text = "";
                            //empClsTotalHours.Text = "";
                            //empClsHours.Text = "";
                            clsRegDetailId.Text = "";
                            clsRegCode.Text = "";
                            clsName.Text = "";
                            //myDDL1.Items.Clear();
                            //this.myDDL1.Items.Add("");
                            //this.myDDL1.SelectedIndex = myDDL1.Items.IndexOf(myDDL1.Items.FindByValue(""));
                            
                        }

                        break;
                    case "CMD_EDIT":
                       
                        List<emp_cls_mast> crm2 = context.emp_cls_mast.Where("it.[emp_cls_mast_id]=" + empClsMastId.Text + " ").ToList();
                        if (crm2.Count() == 1)
                        {
                            emp_cls_mast crmEdit = context.emp_cls_mast.Where("it.[emp_cls_mast_id]=" + empClsMastId.Text + " ").First();
                            if (crmEdit != null)
                            {
                                crmEdit.emp_id = Convert.ToInt32(empId.Text);
                                crmEdit.emp_cls_day = empClsDay.Text;
                                //crmEdit.emp_cls_total_hours = empClsTotalHours.Text;
                                //crmEdit.emp_cls_start = empClsStart.Text;
                                //crmEdit.emp_cls_end = empClsEnd.Text;
                                //crmEdit.emp_cls_hours = empClsHours.Text;
                                 //ecm.cls_reg_detail_id = Convert.ToInt32(clsRegDetailId.Text);
                                //crmEdit.cls_reg_detail_id = Convert.ToInt32(myDDL1.SelectedValue);


                                if (context.SaveChanges() > 0)
                                {

                                    ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
                                    BindData();
                                    //GridView2.DataBind();
                                    GridView2.SelectedIndex = -1;
                                    //CMD_INSERT.Enabled = true;
                                    //CMD_EDIT.Enabled = false;
                                    empCode.Text = "";
                                    empName.Text = "";
                                    empClsDay.Text = "";
                                    empId.Text = "";
                                    //empClsEnd.Text = "";
                                    //empClsStart.Text = "";
                                    //empClsTotalHours.Text = "";
                                    clsRegDetailId.Text = "";
                                    clsRegCode.Text = "";
                                    clsName.Text = "";
                                    //empClsHours.Text = "";
                                    //myDDL1.Items.Clear();
                                    //CMD_INSERT.Enabled = true;
                                    //CMD_EDIT.Enabled = false;
                                    //this.myDDL1.Items.Add("");
                                    //this.myDDL1.SelectedIndex = myDDL1.Items.IndexOf(myDDL1.Items.FindByValue(""));
                                   
                                }
                            }
                        }
                        break;
                }
            }
        }
        private void clear()
        {
            empCode.Text = "";
            empName.Text = "";
            empClsDay.Text = "";
            empId.Text = "";
            //empClsEnd.Text = "";
            //empClsStart.Text = "";
            //empClsTotalHours.Text = "";
            clsRegDetailId.Text = "";
            clsRegCode.Text = "";
            clsName.Text = "";
            empClsMastId.Text = "";
            //empClsHours.Text = "";
            //this.myDDL1.DataTextField = "";
            //this.myDDL1.DataValueField = "";
            //this.myDDL1.SelectedIndex = 0;
            //this.myDDL1.Items.Add("");
            //this.myDDL1.SelectedIndex = myDDL1.Items.IndexOf(myDDL1.Items.FindByValue(""));
       
            
            // เครียร์ค่าใน dropdownlist
            //myDDL1.Items.Clear();
            //CMD_INSERT.Enabled = true;
            //CMD_EDIT.Enabled = false;

            
        }

        protected void CMD_CLEAR_Click(object sender, EventArgs e)
        {
            clear();
        }
        void BindData()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


            //CMD_INSERT.Enabled = false;
            //CMD_EDIT.Enabled = true;
            strSQL = "SELECT ecm.emp_id, " +
                     "ecm.emp_cls_day, " +
                     "ecm.emp_cls_start, " +
                     "ecm.emp_cls_end, " +
                     "ecm.emp_cls_total_hours, " +
                     "ecm.emp_cls_hours, " +
                     "ecm.emp_cls_mast_id, " +
                     "em.emp_name, " +
                     "em.emp_code, " +
                     "crd.cls_reg_detail_id, " +
                     "crd.cls_reg_id, " +
                     "crd.cls_course_no, " +
                     "crd.cls_course_time, " +
                     "crd.cls_course_amt, " +
                     "crm.cls_reg_id, " +
                     "crm.cls_reg_code, " +
                     "crm.level, " +
                     "crm.cls_name, " +
                     "concat(crm.cls_name,'(',crd.cls_course_time,')') as name_course "+
                "FROM emp_cls_mast ecm " +
                "LEFT OUTER JOIN emp_mast em " +
                "ON ecm.emp_id = em.emp_id " +
                "LEFT OUTER JOIN cls_register_detail crd " +
                "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id " +
                "LEFT OUTER JOIN cls_register_mast crm " +
                "ON crd.cls_reg_id = crm.cls_reg_id ";



            Conn.Open();
            MySqlDataReader dtReader;
            objCmd = new MySqlCommand(strSQL, Conn);
            dtReader = objCmd.ExecuteReader();

            //*** BindData to GridView ***//
            GridView2.DataSource = dtReader;
            GridView2.DataBind();

            

        }
        protected void GridView2_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
        {

            //CMD_INSERT.Enabled = false;
            //CMD_EDIT.Enabled = true;
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;

            


            strSQL = "SELECT ecm.emp_id,ecm.emp_cls_day,ecm.emp_cls_start,ecm.emp_cls_end,ecm.emp_cls_total_hours,ecm.emp_cls_hours, "+
                     "ecm.emp_cls_mast_id,em.emp_code,em.emp_name,crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time, "+
                     "crd.cls_course_amt,crm.cls_reg_id,crm.cls_reg_code,crm.level,crm.cls_name "+
                     "FROM emp_cls_mast ecm "+
                     "LEFT OUTER JOIN emp_mast em "+
                        "ON ecm.emp_id = em.emp_id "+
                     "LEFT OUTER JOIN cls_register_detail crd "+
                        "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id "+
                     "LEFT OUTER JOIN cls_register_mast crm "+
                        "ON crd.cls_reg_id = crm.cls_reg_id " +
                     "where ecm.emp_cls_mast_id = '" + GridView2.DataKeys[e.NewSelectedIndex].Value + "'";

            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);
            da4 = null;
            Conn.Close();
            Conn = null;
            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                empId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["emp_id"]).ToString();
                empCode.Text = (string)ds4.Tables[0].Rows[i]["emp_code"].ToString();
                empName.Text = (string)ds4.Tables[0].Rows[i]["emp_name"].ToString();
                empClsDay.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_day"].ToString();
                //empClsStart.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_start"].ToString();
                //empClsEnd.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_end"].ToString();
                //empClsTotalHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_total_hours"].ToString();
                clsRegCode.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_code"].ToString();
                clsName.Text = (string)ds4.Tables[0].Rows[i]["cls_name"].ToString();
                clsRegDetailId.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                empClsMastId.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_mast_id"].ToString();
                clsCourseTime.Text = (string)ds4.Tables[0].Rows[i]["cls_course_time"].ToString();
                //empClsHours.Text = (string)ds4.Tables[0].Rows[i]["emp_cls_hours"].ToString();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[0]["cls_reg_detail_id"].ToString();
                
                string strSQL2;
                strSQL2 = "SELECT * FROM  cls_register_detail crd  " +
                     "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  " +
                     "where crm.cls_reg_code = '" + clsRegCode.Text + "  '";



                MySqlConnection Conn1 = new MySqlConnection(StrConn);
                Conn1.Open();
                MySqlDataAdapter da2 = new MySqlDataAdapter(strSQL2, Conn1);
                DataSet ds2 = new DataSet();
                
                da2.Fill(ds2);
                //*** DropDownList ***//
                //this.myDDL1.DataSource = ds2;
                //this.myDDL1.DataTextField = "cls_course_no";
                //this.myDDL1.DataValueField = "cls_reg_detail_id";
                //this.myDDL1.DataBind();
                //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                da2 = null;
                Conn1.Close();
                Conn1 = null;
                //dtAdapter = null;
                //objConn.Close();
                //objConn = null;
               

                //int _row2 = ds2.Tables[0].Rows.Count;
                //int j = 0;
                //if (_row2 > j)
                //{
                    
                    
                    //this.myDDL1.SelectedValue = "cls_reg_detail_id";
                    //this.myDDL1.SelectedItem = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                    
                    //this.myDDL1.SelectedValue = (string)ds4.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                    //this.myDDL1.DataBind();	
                    //this.myDDL1.DataSource = ds4;
                    //this.myDDL1.DataTextField = (string)ds2.Tables[0].Rows[i]["cls_course_no"].ToString();
                    //this.myDDL1.DataValueField = (string)ds2.Tables[0].Rows[i]["cls_reg_detail_id"].ToString();
                    
                    //this.myDDL1.DataTextField = "cls_course_no";
                    //this.myDDL1.DataValueField = "cls_reg_detail_id";
                     
                //}
                
            }
            
            
        }
        protected void GridView4_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;
            strSQL = "DELETE FROM emp_cls_mast WHERE emp_cls_mast_id ='" + GridView2.DataKeys[e.RowIndex].Value + "'";
            Conn.Open();
            objCmd = new MySqlCommand(strSQL, Conn);

            objCmd.ExecuteNonQuery();


            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert3", "alert('ลบข้อมูลเรียบร้อยแล้ว')", true);
            GridView2.EditIndex = -1;

            BindData();

        }

        protected void CMD_SEARCH_Click(object sender, EventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;
            String condi = " where 1=1 ";

            if (!empClsDay.Text.Equals(""))
            {
                condi = condi + " and ecm.emp_cls_day = '" + empClsDay.Text + "'";

            }
            if (!empCode.Text.Equals(""))
            {
                condi = condi + " and em.emp_code = '" + empCode.Text + "'";

            }
            if (!empName.Text.Equals(""))
            {
                condi = condi + " and em.emp_name = '" + empName.Text + "'";

            }
            if (!clsRegCode.Text.Equals(""))
            {
                condi = condi + " and crm.cls_reg_code = '" + clsRegCode.Text + "'";

            }
            if (!clsName.Text.Equals(""))
            {
                condi = condi + " and crm.cls_name = '" + clsName.Text + "'";

            }


            strSQL = "SELECT ecm.emp_id,ecm.emp_cls_day,ecm.emp_cls_start,ecm.emp_cls_end,ecm.emp_cls_total_hours,ecm.emp_cls_hours, " +
                     "ecm.emp_cls_mast_id,em.emp_code,em.emp_name,crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time, " +
                     "crd.cls_course_amt,crm.cls_reg_id,crm.cls_reg_code,crm.level,crm.cls_name ,concat(crm.cls_name,'(',crd.cls_course_time,')') as name_course " +
                     "FROM emp_cls_mast ecm " +
                     "LEFT OUTER JOIN emp_mast em " +
                        "ON ecm.emp_id = em.emp_id " +
                     "LEFT OUTER JOIN cls_register_detail crd " +
                        "ON ecm.cls_reg_detail_id = crd.cls_reg_detail_id " +
                     "LEFT OUTER JOIN cls_register_mast crm " +
                        "ON crd.cls_reg_id = crm.cls_reg_id " + condi + " ";



            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds = new DataSet();
            da4.Fill(ds, "emp_mast");

            if (ds.Tables[0].Rows.Count > 0)
            {
                GridView2.DataSource = ds.Tables["emp_mast"];
                GridView2.DataBind();

            }
            else
            {
                GridView2.DataSource = null;
                GridView2.DataBind();
                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert1", "alert('ไม่พบข้อมูล')", true);
            }


        }

        protected void GridView4_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            //if (e.Row.RowType == DataControlRowType.DataRow)
            //{

            //    ImageButton ib = (ImageButton)e.Row.FindControl("ImageButton1");
            //    ib.Attributes.Add("onclick", "return confirm('คุณต้องการที่จะลบข้อมูล?');");

            //}
            HyperLink edit = (HyperLink)(e.Row.FindControl("edit"));
            if (edit != null)
            {

                edit.NavigateUrl = "Teacher_Teach.aspx?empClsMastId=" + (string)DataBinder.Eval(e.Row.DataItem, "emp_cls_mast_id").ToString();
                
            }
        }
    }
}